﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
    public class SalesStockDAL: SuperDataBase,ISalesStockDAL
    {
        public DataTable GetSalesStockByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            StringBuilder strCommandText = new StringBuilder();
            DataSet ds;
            try
            {
                strCommandText.AppendFormat(
               "SELECT row_number() over({0}) AS rownum,Fitemid,FCustid,Fdate,FCyID,Fnumber,FEmpName", strOrderBy);
                strCommandText.Append(" FROM T_Sales_Stock ");
                strCommandText.Append(strWhere);

                System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
                DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
                DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
                DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
                DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
                DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
                DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
                DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
                ds = DB.ExecuteDataSet(sqlcommand);
                recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
                pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));
            }
            catch (Exception)
            {

                throw;
            }
            return ds.Tables[0];
        }

        public DataTable GetDetailedDataByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
        {
            recordCount = 0;
            pageCount = 0;
            StringBuilder strCommandText = new StringBuilder();
            DataSet ds;
            try
            {
                strCommandText.AppendFormat(
               "SELECT row_number() over({0}) AS rownum,Fitemid,FSaleStockID,FOrderID,FOrdernumber,FDate,Fproductid,FProductnumber,FProductname,FQTy,FPrice,FAmount,FisR", strOrderBy);
                strCommandText.Append(" FROM T_Sales_StockEntry ");
                strCommandText.Append(strWhere);

                System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
                DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
                DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
                DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
                DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
                DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
                DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
                DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
                ds = DB.ExecuteDataSet(sqlcommand);
                recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
                pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));


            }
            catch (Exception)
            {

                throw;
            }
            return ds.Tables[0];
        }

        public string Export2Excel(string strWhere)
        {
            StringBuilder strCommandText = new StringBuilder();
            StringBuilder returnValue = new StringBuilder();
            IDataReader dataReader;
            try
            {
                strCommandText.AppendFormat(
               "SELECT a.*,b.* from (select * from T_Sales_Stock {0}) as a left join T_Sales_StockEntry AS b ON a.Fitemid = b.FSaleStockID ORDER BY a.Fitemid FOR XML AUTO, root('Root')", strWhere);


                DbCommand sqlcommand = DB.GetSqlStringCommand(strCommandText.ToString());
                dataReader = DB.ExecuteReader(sqlcommand);

                while (dataReader.Read())
                {
                    returnValue.Append(dataReader[0].ToString());
                }
                dataReader.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return returnValue.ToString();
        }
    }
}
